package com.genius.mobile_page.huodong_jiancha_jd.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;


import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.dingding.util.GeniusConfig;
import com.genius.mobile_page.common.ErrorResponseData;
import com.genius.mobile_page.common.ResponseData;
import com.genius.mobile_page.common.SuccessResponseData;
import com.genius.mobile_page.huodong_jiancha_jd.service.Huodong_jiancha_jdService;
import com.genius.mobile_page.pingjia.PingjiaUtil;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.util.DBTools;
import com.genius.workflow.util.DBTools2;
import com.genius.workflow.util.DBUtils;

import cn.hutool.core.util.ObjectUtil;

@Service("huodong_jiancha_jdService")
public class Huodong_jiancha_jdServiceImpl implements Huodong_jiancha_jdService{

	@Override
	public ResponseData getShopWeizhi(JSONObject parms) {
		StringBuilder sql = new StringBuilder("select s1.*,s2.sStoreName shopname"
//				+ ",s3.sEmployeeNO,s3.sEmployeeName,s3.sZoneMagNO,s3.sZoneMagName,s3.sDirectorNO,s3.sDirectorName"
				+ " from "
        		+ "(select shopcode,latitude,longitude,address,(abs(latitude-?)+abs(longitude-?)) cha"
        		+ " from "+GeniusConfig.JINDUXUNDIANDATABASE+".shop_jingwei a where abs(latitude-?) <= 0.1 and abs(longitude-?) < 0.1) s1 "
        		+ " join "
        		+ "(select sStoreNO,sStoreName from "+GeniusConfig.JINDUXUNDIANDATABASE+".dict_tuserstorehistory"
        				+ " where sStoreNO not in (select shopcode from huodong_jiancha_jd_every where jianchaDate = CURDATE() and flag != 0)) s2 "
				+ " on s1.shopcode =s2.sStoreNO "
//	            		+ " left join "
//	            		+GeniusConfig.XUNDIANDATABASE+".dict_tuserstorehistory s3 on s1.shopcode =s3.sStoreNO"
        		);
		try {
			JSONArray params = new JSONArray();
			params.add(parms.getString("latitude"));
			params.add(parms.getString("longitude"));
			params.add(parms.getString("latitude"));
			params.add(parms.getString("longitude"));

			
			sql.append(" ");
			if(ObjectUtil.isEmpty(parms.getString("nolimit"))){
				sql.append(" order by cha limit 5");
			}
//			System.out.println(sql);
			JSONArray result = DBUtils.selectArr(sql.toString(),params.toArray());
			if(result != null && result.size() > 0){
				return new SuccessResponseData(result);
			}else{
				return new ErrorResponseData("附近没有门店信息");
			}
			
			
		} catch (SQLException throwables) {
			
			throwables.printStackTrace();
			return new ErrorResponseData("数据库操作失败！！");
		} catch (Exception e) {
			e.printStackTrace();
			return new ErrorResponseData(e.getMessage());
		}
	}

	@Override
	public ResponseData insertHuodong_jiancha_jd_every(JSONObject parms) {
		Connection conn = null;
		ResponseData response = null;
        try {
        	//打开连接
        	conn = DBUtils.getConnect();
        	String shopcode = "";
        	String flag = "";
        	String everyId = "";
        	String sqlCunzai = "select id,shopcode,flag from huodong_jiancha_jd_every"
					+ " where shopcode = '"+parms.getString("shopcode")+"' and jianchaDate = CURDATE()";
			ResultSet cunzaiRs = DBUtils.query(conn, sqlCunzai);
			if(cunzaiRs.next()){
				shopcode = cunzaiRs.getString("shopcode");
				flag = cunzaiRs.getString("flag");
				everyId = cunzaiRs.getString("id");
			}
			cunzaiRs.close();
			//不存在信息
        	if(ObjectUtil.isEmpty(shopcode)){
        		DBUtils.beginTransaction(conn);
            	JSONObject dataJson = new JSONObject();
            	String sql ="insert into huodong_jiancha_jd_every (shopcode,shopname,jianchaDate,shoplatitude,shoplongitude"
            			+ ",shopaddress,latitude,longitude,address,remark,flag,imgurl,lrr,lrsj,maxScore,score) "
    					+ "values ('"+parms.getString("shopcode")+"','"+parms.getString("shopname")+"',now()"
    					+ ",'"+parms.getString("shoplatitude")+"','"+parms.getString("shoplongitude")+"'"
    					+ ",'" + parms.getString("shopaddress") + "','" + parms.getString("latitude") + "'"
    					+ ",'"+parms.getString("longitude")+"','"+parms.getString("address")+"'"
    					+ ",'"+parms.getString("remark")+"','"+parms.getString("flag")+"','"+parms.getString("imgurl")+"'"
    					+ ",'"+parms.getString("userId")+"',now(),0,0)";
            	if(DBUtils.commitOne(conn, sql)){
            		String sqlId = "select last_insert_id() id";
    				ResultSet rs = DBUtils.query(conn, sqlId);
    				if(rs.next()){
    					dataJson.put("everyId", rs.getString("id"));
    				}
    				rs.close();
            	}
            	DBUtils.endTransaction(conn);
            	response = new SuccessResponseData(dataJson);
        	}else{
        		//未检查状态返回id
        		if(ObjectUtil.equals("0", flag)){
        			JSONObject dataJson = new JSONObject();
        			dataJson.put("everyId", everyId);
        			response = new SuccessResponseData(dataJson);
        		}else{
        			response = new ErrorResponseData("此门店今天已经检查，请勿重复检查");
        		}
        	}
        	
        } catch (SQLException throwables) {
			
			throwables.printStackTrace();
			response = new ErrorResponseData("门店检查失败");
		} catch (Exception e) {
			e.printStackTrace();
			response = new ErrorResponseData(e.getMessage());
        } finally {
        	DBUtils.closeDBResources(conn);
		}
		return response;
	}
	
	@Override
	public ResponseData getHuodong_jiancha_jd_item(JSONObject parms) {
//		System.out.println(sql);
		try {
			
			StringBuilder sql = new StringBuilder("select * from huodong_jiancha_jd_item where flag = ? order by sort");
			JSONArray result = DBUtils.selectArr(sql.toString(),new String[]{parms.getString("flag")});
			if(result != null){
				return new SuccessResponseData(result);
			}else{
				return new ErrorResponseData("没有检查条款的信息");
			}
			
			
		} catch (SQLException throwables) {
			
			throwables.printStackTrace();
			return new ErrorResponseData("数据库操作失败！！");
		} catch (Exception e) {
			e.printStackTrace();
			return new ErrorResponseData(e.getMessage());
		}
	}

	@Override
	public ResponseData replaceHuodong_jiancha_jd_every_item(JSONObject parms) {
		try {
			LinkedList<SQLBean> sqlList = new LinkedList<SQLBean>();
			String itemTable = "huodong_jiancha_jd_every_item";
			String itemField = "everyId,shopcode,itemid,itemname,itembiaozhun,maxScore,score,remark,imgurl,jianchaDate=now()"
					+ ",lrr,lrsj=now()";
			String itemSql = DBTools.getReplaceSql(itemField, itemTable);
			List<String[]> itemParms = DBTools.getInsertListParms(itemField, parms.getJSONArray("dataArray"));
			SQLBean itemSqlBean = new SQLBean(itemSql, itemParms);
			sqlList.add(itemSqlBean);
			
			String everyTable = "huodong_jiancha_jd_every";
			String everyField = "flag,maxScore,score";
			String everySql = DBTools.getUpdateAndWhereSql(everyField, everyTable, "id");
			String[] everyParam = DBTools.getUpdateAndWhereParms(everyField, parms, "id");
				
			SQLBean everySqlBean = new SQLBean(everySql, everyParam);
			sqlList.add(everySqlBean);
			
			DBTools2.insertBatch(sqlList);
//			DBTools.replaceBatch("everyId,shopcode,itemid,itemname,optionid,optionname,otheroptionid,otheroptionname"
//					+ ",remark,imgurl,lrr,lrsj=now()", "siji_qiandao_every_item", parms.getJSONArray("dataArray"));
//			
//			DBTools2.update("flag", "id", "siji_qiandao_every", parms);
//			
			parms.put("pingjiaType", "6");
			PingjiaUtil.insertTypePingjia(parms);
			return new SuccessResponseData();
			
		} catch (SQLException throwables) {
			
			throwables.printStackTrace();
			return new ErrorResponseData("活动检查保存失败");
		} catch (Exception e) {
			e.printStackTrace();
			return new ErrorResponseData(e.getMessage());
		}
	}

	@Override
	public ResponseData getHuodong_jiancha_jd_every(JSONObject parms) {
		try {
			JSONArray params = new JSONArray();
			StringBuilder sql = new StringBuilder("select *,(select name from "
					+GeniusConfig.JINDUZHIBIAODATABASE+".s_userinfo where id = a.lrr)lrrname"
							+ " from huodong_jiancha_jd_every a"
					+ " where jianchaDate >= ? and jianchaDate <= ? ");
			params.add(parms.getString("startDate"));
			params.add(parms.getString("endDate"));
			if(ObjectUtil.isNotEmpty(parms.getString("shopcode"))){
				sql.append(" and shopcode like ?");
				params.add("%"+parms.getString("shopcode")+"%");
			}
			if(ObjectUtil.isNotEmpty(parms.getString("flag"))){
				sql.append(" and flag = ?");
				params.add(parms.getString("flag"));
			}
			if(ObjectUtil.isNotEmpty(parms.getString("notflag"))){
				sql.append(" and flag != ?");
				params.add(parms.getString("notflag"));
			}
			if(ObjectUtil.isNotEmpty(parms.getString("lrr"))){
				sql.append(" and lrr = ?");
				params.add(parms.getString("lrr"));
			}
			
			
			JSONArray result = DBUtils.selectArr(sql.toString(),params.toArray());
			if(result != null){
				return new SuccessResponseData(result);
			}else{
				return new ErrorResponseData("没有活动检查的信息");
			}
			
			
		} catch (SQLException throwables) {
			
			throwables.printStackTrace();
			return new ErrorResponseData("数据库操作失败！！");
		} catch (Exception e) {
			e.printStackTrace();
			return new ErrorResponseData(e.getMessage());
		}
	}

	@Override
	public ResponseData getHuodong_jiancha_jd_every_item(JSONObject parms) {
		try {
			JSONArray params = new JSONArray();
			StringBuilder sql = new StringBuilder("select * from huodong_jiancha_jd_every_item a"
					+ " where 1=1 ");
			if(ObjectUtil.isNotEmpty(parms.getString("startDate"))){
				sql.append(" and jianchaDate >= ? and jianchaDate <= ?");
				params.add(parms.getString("startDate"));
				params.add(parms.getString("endDate"));
			}
			if(ObjectUtil.isNotEmpty(parms.getString("shopcode"))){
				sql.append(" and shopcode like ?");
				params.add("%"+parms.getString("shopcode")+"%");
			}
			if(ObjectUtil.isNotEmpty(parms.getString("everyId"))){
				sql.append(" and everyId = ?");
				params.add(parms.getString("everyId"));
			}
			if(ObjectUtil.isNotEmpty(parms.getString("lrr"))){
				sql.append(" and lrr = ?");
				params.add(parms.getString("lrr"));
			}
			
			
			JSONArray result = DBUtils.selectArr(sql.toString(),params.toArray());
			if(result != null){
				return new SuccessResponseData(result);
			}else{
				return new ErrorResponseData("没有活动检查详情的信息");
			}
			
			
		} catch (SQLException throwables) {
			
			throwables.printStackTrace();
			return new ErrorResponseData("数据库操作失败！！");
		} catch (Exception e) {
			e.printStackTrace();
			return new ErrorResponseData(e.getMessage());
		}
	}

	
	
	
}

